# -*- coding: UTF-8 -*-
# Author: Damon(wuud1)
# CreateDate: 
# Message:  # 把零件的过滤条件分割

from MysqlTools import MysqlTools
import json
import re
from try_ import format_ex


class Tools:

    def __init__(self, **kwargs):
        self.db = MysqlTools(db_config=kwargs.get("db_config"), type='dict', auto_commit=10000)
        self.parts_num_table = kwargs.get("parts_num_table")
        self.sa_desc_table = kwargs.get("sa_desc_table")
        self.vin_table = kwargs.get("vin_table")

    def get_parts_list(self, table):
        sql = '''select `extra_info` from `{}`'''.format(table)
        data = self.db.select_all(sql)
        return data

    def run(self):
        parts_list = self.get_parts_list(self.parts_num_table)
        for info in parts_list:
            extra_info = json.loads(info.get("extra_info"))
            microappl = extra_info.get("microappl")
            if microappl.strip() == "":
                continue
            else:
                _dict = dict(
                    extra_info=microappl
                )
                in_sql = self.db.get_insert_sql('extra_info', _dict)
                self.db.execute_sql(in_sql)
        self.db.commit_sql()

    def run_vin(self):
        parts_list = self.get_parts_list(self.vin_table)
        _list = []
        for info in parts_list:
            extra_info = json.loads(info.get("extra_info"))
            except_l = extra_info.get("except")
            shoud_l = extra_info.get("should")
            for e in except_l:
                e = '不带'+e
                _list.append(e)
            for s in shoud_l:
                _list.append(s)
        _s = set(_list)
        for i in _s:
            _dict = dict(
                desc=i
            )
            in_sql = self.db.get_insert_sql('all_desc_vin_unique', _dict)
            self.db.execute_sql(in_sql)
        self.db.commit_sql()

    def clean_desc(self):
        sql = '''select * from `all_desc_unique`;'''
        data = self.db.select_all(sql)
        for info in data:
            desc = info.get("desc")
            new_desc = re.sub(r'终点.*?\d\b|起点.*?\d\b', '', desc).strip('-').strip()
            # new_desc = re.sub(r'除了', '', new_desc).strip('-').strip()
            new_desc = re.sub(r'起点 改进实施日期 \w*\b|终点 改进实施日期 \w*\b', '', new_desc).strip('-').strip()
            if new_desc == '':
                continue

            # new_desc = re.sub(r'终点 OPR \d{5}|起点 OPR \d{5}', '', desc).strip()
            # new_desc = re.sub(r'终点 改进实施日期 \d{7}|起点 改进实施日期 \d{7}', '', new_desc).strip()
            # new_desc = re.sub(r'终点 日期 \d{8}|起点 日期 \d{8}', '', new_desc).strip()
            # new_desc = re.sub(r'终点 发动机 \d{6}|起点 发动机 \d{6}', '', new_desc).strip()

            _dict = dict(
                desc=new_desc
            )
            in_sql = self.db.get_insert_sql('all_desc_no_OPR', _dict)
            self.db.execute_sql(in_sql)
        self.db.commit_sql()

    def construct_sa_desc(self):

        num = 3093
        sql = '''select distinct `desc` from all_desc_vin_unique where `desc` not in (select distinct `desc` from sa_desc);'''
        data = self.db.select_all(sql)
        for info in data:
            desc = info.get("desc").strip()
            sa = 'SA%06d' % (num)
            num += 1
            _dict = dict(
                sa=sa,
                desc=desc
            )
            insql = self.db.get_insert_sql('sa_desc', _dict)
            self.db.execute_sql(insql)
        self.db.commit_sql()

    def get_vin_info(self, table):
        sql = '''select `extra_info` from `{}`'''.format(table)
        data = self.db.select_all(sql)
        _l = []
        for info in data:
            extra_info = json.loads(info.get("extra_info"))
            app_info = extra_info.get("app_info")
            for i in app_info:
                _l.append(i)
        return set(_l)

    def all_clean(self):
        # '一步清洗到位'
        parts_list = self.get_parts_list(self.parts_num_table)
        all_list = []
        for info in parts_list:
            extra_info = json.loads(info.get("extra_info"))
            microappl = extra_info.get("microappl").strip('-').strip(" ").replace(' 或者 ', '#or#').replace(' 和 ', '#and#').replace('除了', '#not#')
            # or分割
            or_list = microappl.split("#or#")
            for i in or_list:
                # and分割
                and_list = i.split("#and#")
                for j in and_list:
                    # not分割
                    not_list = j.split("#not#")
                    for k in not_list:
                        all_list.append(k.strip("-").strip())
        unique_list = set(all_list)
        _l = []
        for i in unique_list:
            new_desc = re.sub(r'终点.*?\d\b|起点.*?\d\b', '', i).strip('-').strip()
            new_desc = re.sub(r'起点 改进实施日期 \w*\b|终点 改进实施日期 \w*\b', '', new_desc).strip('-').strip()
            if new_desc == '':
                continue
            _l.append(new_desc)
        _s = set(_l)

        # 取当前车架号表里的所有条件 app_info
        _vin_info = self.get_vin_info(self.vin_table)

        _all = _s.union(_vin_info)

        num = 666
        for desc in _all:
            desc = desc.strip("-").strip()
            sa = 'SA%06d' % num
            num += 1
            _dict = dict(
                sa=sa,
                desc=desc
            )
            insql = self.db.get_insert_sql('sa_desc', _dict)
            self.db.execute_sql(insql)
        self.db.commit_sql()


    def format_ex(self):

        sql = '''select * from `extra_info`;'''
        data = self.db.select_all(sql)
        for info in data:
            id = info.get("id")
            ex = info.get("extra_info")
            try:
                new_ex = format_ex(ex)
            except Exception as e:
                print(e.__repr__())
                new_ex = []
            upsql = '''update `extra_info` set `format_ex`='{}' where `id`="{}";'''.format(json.dumps(new_ex, ensure_ascii=False), id)
            self.db.execute_sql(upsql)
        self.db.commit_sql()

    def get_struc_info(self):
        sql = '''select `desc` from `struc_desc`;'''
        data = self.db.select_all(sql)
        _set = set()
        for info in data:
            desc = info.get("desc")
            if desc == '':
                continue
            else:
                _set.add(desc)
        return _set

    def construct_sa_desc_new(self):
        # 从格式化ex中提取desc并且设置sa
        sql = '''select `format_ex` from `extra_info`;'''
        data = self.db.select_all(sql)
        _all = []
        for info in data:
            format_ex = json.loads(info.get("format_ex"))
            for d in format_ex:
                exc = d.get("exc")
                app_sa = d.get("app_sa")
                for e in exc:
                    _all.append(e)
                for a in app_sa:
                    _all.append(a)
        _s = set(_all)
        _vin_info = self.get_vin_info(self.vin_table)
        _struc_info = self.get_struc_info()
        _all = _s.union(_vin_info).union(_struc_info)

        num = 666
        for desc in _all:
            desc = desc.strip("-").strip()
            if desc == '':
                continue
            sa = 'SA%06d' % num
            num += 1
            _dict = dict(
                sa=sa,
                desc=desc
            )
            insql = self.db.get_insert_sql('sa_desc', _dict)
            self.db.execute_sql(insql)
        self.db.commit_sql()

    def get_struc_rules(self):
        '''lee的数据清洗错误, 没有用的函数'''

        sql = '''select `extra_info` from `cars_structure_collection`'''
        data = self.db.select_all(sql)
        _l = []
        for info in data:
            try:
                extra_info_l = json.loads(info.get("extra_info"))
            except:
                continue
            if not isinstance(extra_info_l, list):
                continue
            else:
                for extra_info in extra_info_l:
                    exc = extra_info['except']['att']
                    app = extra_info['should']['att']
                    exc = map(lambda x: '不带'+x, exc)
                    app.extend(exc)
                    for d in app:
                        _l.append(d)
        _s = set(_l)
        for desc in _s:
            if desc == '':
                continue
            else:
                insql = self.db.get_insert_sql('struc_desc', {"desc": desc})
                self.db.execute_sql(insql)
        self.db.commit_sql()

    def clean_struc_description(self):
        '''取分组description字段,格式化'''
        sql = '''select `description` from `cars_structure_collection`;'''
        data = self.db.select_all(sql)
        for info in data:
            description = info.get("description")
            if description.strip() == '':
                continue
            _l = format_ex(description)
            ext = json.dumps(_l, ensure_ascii=False)
            _dict = {
                "extra_info": description,
                "format_ex": ext
            }
            in_sql = self.db.get_insert_sql('struc_extra_info', _dict)
            self.db.execute_sql(in_sql)
        self.db.commit_sql()

    def get_struc_desc(self):
        sql = '''select `format_ex` from `struc_extra_info`;'''
        data = self.db.select_all(sql)
        _l = []
        for info in data:
            format_ex = json.loads(info.get("format_ex"))
            for ex in format_ex:
                exc = ex.get("exc")
                app_sa = ex.get("app_sa")
                for e in exc:
                    if e == '':
                        continue
                    _l.append(e)
                for a in app_sa:
                    if a == '':
                        continue
                    _l.append(a)
        _s = set(_l)
        for desc in _s:
            insql = self.db.get_insert_sql('struc_desc', {"desc": desc})
            self.db.execute_sql(insql)
        self.db.commit_sql()


if __name__ == '__main__':
    db_config = dict(
        host="192.168.10.166",
        port=3306,
        user="a",
        passwd="110",
        db="citroen",
        charset="utf8",

    )
    parts_num_table = 'cars_part_num'
    sa_desc_table = 'all_desc'
    vin_table = 'cars_vin_info_new'
    tools = Tools(db_config=db_config, parts_num_table=parts_num_table, sa_desc_table=sa_desc_table, vin_table=vin_table)
    # tools.run()
    # tools.run_vin()
    # tools.clean_desc()
    # tools.construct_sa_desc()
    # tools.all_clean()
    # tools.format_ex()
    tools.construct_sa_desc_new()
    # tools.get_struc_rules()
    # tools.clean_struc_description()
    # tools.get_struc_desc()